Open Data Protocol (OData) is an open protocol that allows the creation and consumption of URL links that allow users to execute Pyramid queries and download the results from other applications, such as Excel. This provides access to up to date and accurate data through the Pyramid platform and extends the data and content governance and "one source of truth" to other applications.
Get the OData Options
Basic users can copy one of the OData URL options for any content in a live presentation, to use in any other OData compliant tools:
- Right-click the visual and select Content Details > OData > [OData Option].
Note: If you are not displaying the new Context Menu, this link might not be under Content Details.
The link is copied to your clipboard, and can be opened in other applications. Note that no notification is displayed to indicate that the link has been copied.
Tip: If you have access to the Content Manager, you can also copy the OData Links or Snippets from the Tab Menus and Metadata Panel. For more information, see OData Feed.
OData Options
- OData Link: copy the OData URL link for the item. The OData link enables connectivity with datasources like Excel using Windows or Basic authentication.
- OData Link without Filters: copy the OData URL link for the item, excluding filters.
- OData Snippet: copy the OData snippet for the item. Note: The snippet enables connectivity with datasources like Excel or Power BI using Web Token authentication.
- OData Snippet without Filters: copy the OData snippet for the item, excluding filters.
Warning: If you select to copy the link or snippet without filters, the returned data will not be limited by the Query Limits. Use at your own risk.
Using OData in Excel
Example: Using the OData Link in Excel
To use Pyramid data inside another tool, like Excel, copy one of the OData Link options. This will copy the special URL to your clipboard.
Next, switch to Excel, and open a new worksheet. From the Data tab, choose Get Data > From OData Feed (orange arrow below).
In the pop-up dialog, paste the URL into the box (CTRL+V) and click OK.
You will be prompted to supply your user credentials to login to Pyramid and access the data source. Pyramid supports Windows and Basic Authentication. Enter your credentials and click Connect.
You will then be prompted with a preview of the result set. Click Load to import the data.
Once imported, use the data as needed. At any time, right-click on the table to Refresh the data and re-pull updated results from Pyramid.
Example: Using the OData Snippet in Excel
If you want to use Pyramid data inside another tool but need to connect using web token authentication, you need to copy one of the OData Snippet options. This will copy the special snippet to your clipboard for use in Excel.
Next, switch to Excel, and open a new worksheet. Go to the Data tab, choose Get Data > From Other Sources > Blank Query.
Paste your snippet URL into the text box (red arrow):
Click Edit Credentials and, in the dialog that opens, select Web API (red arrow).
Copy the appropriate Authentication key from Pyramid to your clipboard:
- If you want to authenticate as the user that is currently logged in to Pyramid, go to the System Info page and click the Copy icon alongside the Authentication token (red arrow, below).
- If you are an administrator, you can authenticate as a different user via the Admin Console. For details, see Get Authentication Token.
Go back to the OData feed dialog in Excel, and paste the Authentication key into the Key field. Click Connect.
A preview of the result set opens in the Query Builder. Click Close & Load to import the data.